package com.xxx.mysql.student.mangaer;

import org.omg.Messaging.SyncScopeHelper;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;

public class OperationStudent {

    static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    public static final String URL = "jdbc:mysql://localhost:3306/practice_1";
    public static final String USER = "root";
    public static final String PASSWORD = "88888888";

    public static Connection getConn() throws ClassNotFoundException, SQLException {

        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);

        return connection;

    }

    public static void close(Connection conn , Statement stat , ResultSet res){
        try {
            if (res != null ){
                res.close();
            }
            if(stat != null){
                stat.close();
            }
            if (conn != null){
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }


    /**
     * 添加学生信息
     */
    public static void addStudent(StudentInfo stInfo)  {
        Connection conn = null;
        Statement statement = null;
        try {
            conn = OperationStudent.getConn();

            statement = conn.createStatement();

            String sql = "INSERT INTO student VALUES (?, ? , ? , ? , ?)";

            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setString(1, stInfo.getStudentId());

            ps.setString(2, stInfo.getStudentName());

            ps.setString(3, stInfo.getStudentSex());

            ps.setDate(4, new Date(stInfo.getStudentBirthday().getTime()));

            ps.setString(5, stInfo.getClassId());

            int r1 = ps.executeUpdate();

            if (r1 >= 1){
                System.out.println("添加成功");
                return;
            }
            System.out.println("添加失败");


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            OperationStudent.close(conn, statement, null);
        }
    }

    /**
     * 删除学生信息
     */
    public static void deleteStudent(int id){

        Connection conn = null;
        Statement statement = null;
        try {
            conn = OperationStudent.getConn();
            statement = conn.createStatement();
            String sql = "DELETE FROM student WHERE SNO = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            int flag = ps.executeUpdate();
            if (flag >= 1) {
                System.out.println("删除成功");
                return;
            }
            System.out.println("未找到此ID，删除失败");

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            OperationStudent.close(conn, statement, null);
        }
    }

    /**
     * 修改学生信息
     */
    public static void updateStudent(int id2 , StudentInfo st1){

        Connection conn = null;
        Statement statement = null;
        try {
            conn = OperationStudent.getConn();
            statement = conn.createStatement();
            String sql = "UPDATE student SET SNO = ? , SNAME = ? , SSEX = ? , SBIRTHDAY = ? , CLASS = ? WHERE SNO = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, st1.getStudentId());
            ps.setString(2, st1.getStudentName());
            ps.setString(3, st1.getStudentSex());
            ps.setDate(4, new Date(st1.getStudentBirthday().getTime()));
            ps.setString(5, st1.getClassId());
            ps.setInt(6, id2);
            int flag = ps.executeUpdate();
            if (flag >= 1) {
                System.out.println("修改成功");
                return;
            }
            System.out.println("未找到此ID，删除失败");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            OperationStudent.close(conn, statement, null);
        }


    }

    /**
     * 查询学生信息
     */
    public static void queryStudent(int id3) {
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            conn = OperationStudent.getConn();
            statement = conn.createStatement();
            String sql3 = "SELECT * FROM student WHERE SNO = ?";
            PreparedStatement ps = conn.prepareStatement(sql3);
            ps.setInt(1, id3);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                String sno = resultSet.getString("SNO");
                String sname = resultSet.getString("SNAME");
                String ssex = resultSet.getString("SSEX");
                Date sbirthday1 = resultSet.getDate("SBIRTHDAY");
                String aClass = resultSet.getString("CLASS");
                System.out.println(sno);
                System.out.println(sname);
                System.out.println(ssex);
                System.out.println(sbirthday1);
                System.out.println(aClass);
            }


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            OperationStudent.close(conn, statement,resultSet);
        }

    }

}
